--member
create table safari_member(
	id varchar2(100) primary key,
	password varchar2(100) not null,
	name varchar2(100) not null,
	address varchar2(500) not null,
	birthday number not null,
	gender varchar2(100) not null,
	cellphone varchar2(100) not null,
	email varchar2(100) unique,
	grade varchar2(100) not null,
	point number not null
)

--회원가입
insert into safari_member(id,password,name,address,birthday,gender,cellphone,email,grade,point) 
values('duwjddlek','1','여정','BUSAN',19900908,'female','01093879027','duwjddlek@naver.com','customer',5000);
insert into safari_member(id,password,name,address,birthday,gender,cellphone,email,grade,point) 
values('audrlek','1','명기','YONGIN',19871226,'male','01049381326','audrlek@naver.com','customer',5000);

--확인용
select * from SAFARI_MEMBER;
select * from SAFARI_MEMBER WHERE ID='duwjddlek';

--회원탈퇴
delete from safari_member where id='v';

--회원정보 수정
update SAFARI_MEMBER set name='ettto',address='efwfwe' where id='et'

--쪽지함 생성
create table safari_message(
message_no number primary key,
senderId varchar2(100) not null,
receiverId varchar2(100) not null,
message_content varchar2(2000) not null,
send_date date not null,
constraint fk_senderId foreign key(senderId) references safari_member(id)
)

--쪽지함의 message_no 용 sequence
create sequence seq_message;

--새 쪽지 보내기(insert)
insert into safari_message(message_no,senderId,receiverId,message_content,send_date) values(seq_message.nextval,'sdf','dsddf','this is test content',sysdate);

--쪽지함 확인용
select * from safari_message;

--내 받은 쪽지 select
select * from safari_message where senderId='et'

--받는 사람 id 존재여부 확인
select count(*) from SAFARI_MEMBER where id='dsf'

--받은 메세지 갯수
select count(*) from safari_message where receiverid='dsf'

--받은 메세지 가져오기
select message_no,senderId,message_content,to_char(send_date,'YY-MM-DD HH24:mm:ss') as send_date from SAFARI_MESSAGE where receiverId='dsf'

-- 받은 메세지 해당 페이지별로 가져오기
select  message_no,senderId,message_content,send_date from (
select message_no,senderId,message_content,send_date,
ceil(rownum/10) as page from (
select  message_no,senderId,message_content,to_char(send_date,'YY-MM-DD HH24:mm:ss') as send_date
from safari_message where receiverId='dsf'
order by message_no desc
)
)where page=1


--확인용
	   select message_no,senderId,receiverId,message_content,send_date from (
select message_no,senderId,receiverId,message_content,send_date,
ceil(rownum/10) as page from (
select  message_no,senderId,receiverId,message_content,to_char(send_date,'YY-MM-DD HH24:mm:ss') as send_date
from safari_message where receiverId='dsf'
order by message_no desc
)
)where page='1'